package com.situ.hotel.dao.impl;

import com.situ.hotel.dao.IEmpDao;
import com.situ.hotel.pojo.Emp;
import com.situ.hotel.pojo.query.EmpQuery;
import com.situ.hotel.pojo.vo.EmpDeptVO;
import com.situ.hotel.utils.JDBCUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

public class EmpDaoImpl implements IEmpDao {
    private JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtil.getDataSource());

    @Override
    public List<Emp> selectAll() {
        String sql = "select id,name,dept_id,email,phone from emp";
        List<Emp> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
        return list;
    }

    @Override
    public List<EmpDeptVO> selectByPage(EmpQuery empQuery) {
        String sql = "SELECT e.id,e.name,e.email,e.phone,d.id as deptId, d.name as deptName\n" +
                "FROM emp AS e INNER JOIN dept AS d\n" +
                "on e.dept_id=d.id ";

        //查询参数
        List<Object> args = new ArrayList<>();
        String where = "where 1=1 ";
        if (!StringUtils.isEmpty(empQuery.getName())) {
            where += "and name like ?";
            args.add("%" + empQuery.getName() + "%");
        }
        if (!StringUtils.isEmpty(empQuery.getEmail())) {
            where += "and email=?";
            args.add(empQuery.getEmail());
        }
        if (!StringUtils.isEmpty(empQuery.getPhone())) {
            where += "and phone=?";
            args.add(empQuery.getPhone());
        }

        String limit = "";
        if (empQuery != null) {
            int offset = (empQuery.getPage() - 1) * empQuery.getLimit();
            limit = "order by id desc limit " + offset + "," + empQuery.getLimit();
        }

        List<EmpDeptVO> list = jdbcTemplate.query(sql + where + limit, new BeanPropertyRowMapper<EmpDeptVO>(EmpDeptVO.class), args.toArray());
        return list;
    }

    @Override
    public Long selectTotalCount(EmpQuery empQuery) {
        // <where> <if></if> </where>
        //这三个搜索条件应该是有值才拼接上，没有值就不拼接
        String sql = "select count(*) from emp ";

        //查询参数
        List<Object> args = new ArrayList<>();
        String where = "where 1=1 ";
        if (!StringUtils.isEmpty(empQuery.getName())) {
            where += "and name like ?";
            args.add("%" + empQuery.getName() + "%");
        }
        if (!StringUtils.isEmpty(empQuery.getEmail())) {
            where += "and email=?";
            args.add(empQuery.getEmail());
        }
        if (!StringUtils.isEmpty(empQuery.getPhone())) {
            where += "and phone=?";
            args.add(empQuery.getPhone());
        }

        Long totalCount = jdbcTemplate.queryForObject(sql + where, Long.class, args.toArray());
        return totalCount;
    }

    @Override
    public Integer deleteById(Integer id) {
        String sql = "delete from emp where id=?";
        int count = jdbcTemplate.update(sql, id);
        return count;
    }

    @Override
    public Integer deleteAll(Integer[] ids) {
        //[14,15]
        // delete from emp where id in(?,?)
        String sql = "delete from emp where id in(";
        //sql += Stream.of(ids).map(item -> "?").collect(Collectors.joining(","));
        for (Integer id : ids) {
            sql += "?,";
        }
        //delete from emp where id in(?,?,
        sql = sql.substring(0, sql.length() - 1);
        sql += ")";
        int count = jdbcTemplate.update(sql, ids);
        return count;
    }

    @Override
    public Integer add(Emp emp) {
        String sql = "insert into emp(name,dept_id,email,phone) values(?,?,?,?)";
        int count = jdbcTemplate.update(sql, emp.getName(), emp.getDeptId(), emp.getEmail(), emp.getPhone());
        return count;
    }

    @Override
    public Emp selectById(int id) {
        String sql = "select id,name,dept_id,email,phone from emp where id=?";
        // Emp emp = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Emp>(Emp.class), id);
        List<Emp> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class), id);
        if (CollectionUtils.isEmpty(list)) {
            return null;
        }
        return list.get(0);
    }

    @Override
    public Integer update(Emp emp) {
        String sql = "update emp set name=?,dept_id=?,email=?,phone=? where id=?";
        int count = jdbcTemplate.update(sql, emp.getName(), emp.getDeptId(), emp.getEmail(), emp.getPhone(), emp.getId());
        return count;
    }
}
